<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<link rel="stylesheet" type="text/css" href="style.css"/>
		<title>O/R Broker v3 User's Guide</title>
	<script type="text/javascript" src="shl/scripts/shCore.js"></script>
	<script type="text/javascript" src="shl/scripts/shBrushJava.js"></script>
	<script type="text/javascript" src="shl/scripts/shBrushScala.js"></script>
	<script type="text/javascript" src="shl/scripts/shBrushSql.js"></script>
	<link type="text/css" rel="stylesheet" href="shl/styles/shCore.css"/>
	<link type="text/css" rel="stylesheet" href="shl/styles/shThemeDefault.css"/>
	<script type="text/javascript">
		SyntaxHighlighter.config.clipboardSwf = 'shl/scripts/clipboard.swf';
		SyntaxHighlighter.all();
	</script>
	</head>
	<body>

<!--====================================================================-->
		<div id="tableOfContents">
			<h1>Table of contents</h1>
			<ol>
				<li><a href="#statements">Statements</a></li>
				<li><a href="#externalizingStatements">Externalizing statements</a></li>
				<li><a href="#addingParameters">Adding parameters</a></li>

				<li><a href="#usingTextReplacement">Using text replacements</a></li>
				<li><a href="#resultObjects">Result objects</a></li>
				<li><a href="#nestedParameters">Nested parameters</a></li>
				<li><a href="#transactions">Transactions</a></li>
				<li><a href="#storedProcedures">Stored procedures</a></li>
				<li><a href="#mappingToExistingObjects">Mapping to existing object instances</a></li>

				<li><a href="#appendingStatements">Appending statements</a></li>
				<li><a href="#inheritance">Inheritance</a></li>
				<li><a href="#dynamicStatements">Dynamic statements</a></li>
				<li><a href="#mappingAssociations">Mapping associations</a></li>
				<li><a href="#lazyInstantiation">Lazy instantiation</a></li>
				<li><a href="#circularReferences">Circular references</a></li>

			</ol>
		</div>
<!--====================================================================-->
		<div id="statements">
			<h1>SQL Statements</h1>
			<p>Let's start with a simple example. The following SQL statement selects the highest employee id.</p>
			<pre class="brush: sql;">SELECT MAX(EmployeeId) FROM Employee
</pre>
			<p>This is saved <em>somewhere</em> in a file named <tt>getMaxEmployeeID.sql</tt>. The name of the file (without the type suffix) is also the id of that statement. The following Scala code sets up the Broker and runs the query.</p>
			<pre class="brush: scala;">val sqlFolder = new java.io.File("/somewhere")
val builder = new SQLFileBuilder(myDataSource, sqlFolder)
val broker = builder.build

val maxID = broker.readOnly() { session =>
  session.selectOne('getMaxEmployeeID)
}: Option[Int]

maxID match {
  case Some(id) => println("Max employee id: " + id)
  case None => println("We have no employees yet.")
}
</pre>
<p>In the query above, because only one column has been selected, the particular column type is returned (in this case <tt>scala.Int</tt>). If multiple columns are specifed, a Scala <em>tuple</em> will be returned, e.g. the following <tt>getLastEmployee.sql</tt> file (still selecting only one record):</p>

<pre class="brush: sql;">SELECT 
	EmployeeId,
	EmployeeName,
	Salary,
	Currency,
	SSN
FROM 
	Employee
WHERE
	EmployeeId IN (SELECT MAX(EmployeeId) FROM Employee)</pre>
<p>For the statement above, the Scala code would look like this:</p>
<pre class="brush: scala;">val employee = broker.readOnly() { session =>
  session.selectOne('getLastEmployee)
}: Option[(Int, String, BigDecimal, String, Int)]</pre>
<p>Yep, tuples are nasty to define when having this many elements (and we don't need to live with that), but first let's return more than just one employee. We can change the <tt>WHERE</tt> clause to include a range of employees, e.g. in this <tt>getEmployeesByLowSalary.sql</tt> file:</p>
<pre class="brush: sql;">SELECT 
	EmployeeId,
	EmployeeName,
	Salary,
	Currency,
	SSN
FROM 
	Employee
WHERE
	Salary BETWEEN 12000 AND 18000</pre>
<p>This means that instead of using the pimped method <tt>selectOne</tt>, we can use another pimped method <tt>selectMany</tt>, which returns a <tt>scala.collection.Seq</tt>.</p>
<pre class="brush: scala;">val employees = broker.readOnly() { session =>
  session.selectMany('getEmployeesByLowSalary)
}
for (emp <- employees) println(emp)</pre>
<p>This returns and prints a sequence of employee tuples.</p>
</div>
<!--====================================================================-->

		<div id="addingParameters">
			<h1>Adding parameters</h1>
			<p>Instead of hardcoding the salary range we can specify parameters instead in <tt>getEmployeesBySalary.sql</tt>.</p>
<pre class="brush: sql;">SELECT 
	EmployeeId,
	EmployeeName,
	Salary,
	Currency,
	SSN
FROM 
	Employee
WHERE
	Salary BETWEEN :low AND :high</pre>
<p>Parameters are identified by the preceeding ':' (colon), and are passed as argument to the select method:</p>

<pre class="brush: scala;">var employees = broker.readOnly() { session =>
  session.selectMany('getEmployeesBySalary, "low"->12000, "high"->18000)
}</pre>
		</div>
<!--====================================================================-->
<!--		<div id="usingTextReplacement">
			<h1>Using text replacements</h1>
			<p>Text replacement values are simple string replacements in the SQL statement. They consist of a key wrapped in '{{' and '}}' (double curly brackets). Example:</p>
			<pre class="brush: sql;">&lt;sql-statement id="getEmployees"&gt;

	SELECT 
		EmployeeId,
		EmployeeName,
		Salary,
		Currency,
		SSN
	FROM 
		{{schema}}.Employee
	ORDER BY
		{{sortColumn}}
&lt;/sql-statement&gt;</pre>
			<p>And the Java code could look like this:</p>
			<pre class="brush: scala;">broker.setTextReplacement("schema", "HumanResources");
Query qry = broker.startQuery();
qry.setTextReplacement("sortColumn", "Salary");
List employees;
try {
	 employees = qry.selectMany("getEmployees");
} finally {
	qry.close();
}</pre>
			<p>As you can tell, the replacement values can be set on both the Broker and the Query. If a replacement value should be the same for all statements, e.g. a specific database schema as above, it makes sense to set it on the Broker. Any text replacement set on the Query overrides a replacement of equal name set on the Broker.</p>
		</div>
====================================================================-->
		<div id="rowExtractors">

			<h1>Extractors</h1>
			<p>Now, all this wouldn't really be any fun, if all we had to deal with was tuples. And this is where <em>Extractors</em> come in. A row extractor is the encapsulation of the one-way mapping of a JDBC <tt>ResultSet</tt> row to an object. Because the extraction is written in Scala, you can map a query to any kind of object; immutable class, mutable bean, with constructors or without, anything goes.</p>
			<p>Let's define a simple Employee class intended to work in a multi currency application:</p>
<pre class="brush: scala;">class Employee(val id: Int, var name: String, var SSN: Int) {
  private var _salary: BigDecimal = null
  private var _currency: java.util.Currency = null

  def setSalary(amount: BigDecimal, currency: java.util.Currency) {
    _salary = amount
	_currency = currency
  }
  
  def salary = _salary
  def currency = _currency
}</pre>
			<p>This is obviously a contrived example, but its purpose is to show how mapping is possible to any class design, not just JavaBean style properties, which is so common in many object-relational frameworks. By forcing the salary and currency to be set together, we ensure that the object does not enter an invalid state if someone forgets to set either. This type of defensive design is not possible with the atomic nature of properties in a JavaBean.</p>
<p>A row extractor for the Employee object looks like this:</p>
<pre class="brush: scala;">object EmployeeExtractor extends RowExtractor[Employee] {
  def extract(row: Row) = {
    val id = row.integer("employeeid").get
    val name = row.string("EmployeeName").get
    val ssn = row.integer("SSN").get

    val emp = new Employee(id, name, ssn)

    val salary = row.decimal("SALARY").get
    val currency = java.util.Currency.getInstance(row.string("currency").get)

    emp.setSalary(salary, currency)
    emp
  }
}</pre>
			<p>Notice that all types are wrapped in a <tt>scala.Option</tt> to represent the possibility of a column being <tt>NULL</tt>. In this example all columns are known to be <tt>NOT NULL</tt>, so we safely invoke <tt>get</tt>. </p>
			<p>Now, all we need to do is define the connection from the SQL to this extractor. Using one of our previous files, <tt>getLastEmployee.sql</tt>, it's done in the SQL statement, as a comment, like this:</p>

<pre class="brush: sql;">
-- extractor: my.package.EmployeeExtractor
SELECT 
	EmployeeId,
	EmployeeName,
	Salary,
	Currency,
	SSN
FROM 
	Employee
WHERE
	EmployeeId IN (SELECT MAX(EmployeeId) FROM Employee)</pre>

	<p>With that done, the Scala code should look something like this:</p>
			<pre class="brush: scala;">val employee = broker.readOnly() { session =>
  session.selectOne('getLastEmployee)
}: Option[Employee]
employee match {
  case Some(emp) => println("Our latest employee is " + emp.name)
  caes None => println("We still haven't employed anyone")
}</pre>
		</div>
<!--====================================================================-->
		<div id="nestedParameters">

			<h1>Nested parameters</h1>
			<p>Nested object structures can also be used as parameters. The following statement, <tt>getManagedEmployees.sql</tt>, uses the id value of an Employee object:</p>
<pre class="brush: sql;">SELECT 
	*
FROM 
	Employee
WHERE
	ManagerId = :manager.id</pre>
<p>The Scala code could look like this:</p>
<pre class="brush: scala;">val manager: Employee = ...
val employees = broker.readOnly() { session =>
  session.selectMany('getManagedEmployees, "manager"->manager)
}: Seq[Employee]</pre>
<p>This would return a sequence of Employees whos <tt>ManagerID</tt> matched the provided manager.</p>
		</div>
<!--====================================================================-->
		<div id="transactions">
			<h1>Transactions</h1>
			<p><em>O/R Broker</em> enforces commitment control on all SQL <tt>INSERT/UPDATE/DELETE</tt> statements (and <tt>CALL</tt> statements which perform those same statements) with a transaction isolation level higher than TRANSACTION_NONE. This is done through the <tt>Transactional</tt> class. The following SQL statement, <tt>updateAccountAmount.sql</tt> will update an account with an adjustment amount.</p>
<pre class="brush: sql;">UPDATE
	Account
SET
	Amount = Amount + :adjustment
WHERE
	AccountNumber = :account.accountNumber</pre>
<p>If we were to transfer $500 from one account to another, it could look something like this:</p>

<pre class="brush: scala;">val from = new Account("3453-2343-5675-4337")
val to = new Account("7875-7854-3458-9377")
val amount: BigDecimal = 500.00
broker.transactional() { session =>
  val fromCount = session.execute('updateAccountAmount, "account"->from, "adjustment" -> -amount)
  val toCount = session.execute('updateAccountAmount, "account"->to, "adjustment" -> amount)
  if (fromCount == 1 && toCount == 1) {
    session.commit()
  } else {
    session.rollback()
    println("That didn't work as planned. Weird")
  }
}</pre>
		<p>It is essential that a transaction is always committed (or rolled back) as O/R Broker will otherwise throw a <tt>RollbackException</tt> indicating that the transaction has been rolled back.</p>

		</div>
<!--====================================================================-->
		<div id="storedProcedures">
			<h1>Stored procedures</h1>
			<p>Simple stored procedures that return a result set are generally called like any other query statement, i.e. using one of the <tt>select</tt> methods. but there are differences depending on the type of statement.</p>
			<h2>Modifying</h2>
			<p>Stored procedures that perform modifications, such as INSERT, UPDATE, DELETE, must be called through the <tt>Transactional</tt> session, using either <tt>execute</tt> or <tt>executeBatch</tt> methods.</p>

			<h2>Returning result set</h2>
			<p>If the stored procedure returns a result set, it can be called using one of the <tt>select</tt>  methods of either the <tt>ReadOnly</tt> or <tt>Transactional</tt> object, depending on whether the stored procedure updates data or not.</p>
			<h2>Output parameters</h2>
			<p>A stored procedure that has INOUT/OUT parameters, and does not return a result set, can be called through <tt>callForParms</tt> in which case the output parameters supplied through a callback function.</p>

			<p>Let's say we have stored procedure <tt>GetNewCustomerID(OUT NewID INTEGER)</tt> that returns the next available customer id. Saved to <tt>getNewCustomerID.sql</tt>, the call would look something like this:</p>
			<pre class="brush: sql;">
	{ CALL GetNewCustomerID(:newID) }</pre>
			<p>Since this call updates a table, we want to use a <tt>Transactional</tt> session and remember to commit the transaction.</p>
			<pre class="brush: scala;">val newID = broker.transactional() { session =>
  val id = session.callForParms('getNewCustomerID) { outParms =>
    outParms.integer("newID")
  }
  session.commit()
  id
}</pre>

		</div>
<!--====================================================================-->
		<div id="inheritance">
			<h1>Inheritance</h1>
			<h2>Class inheritance</h2>
			<p>Class inheritance can be handled by adding the attribute <tt>extends</tt> to a <tt>result-object</tt> definition, referencing the id of the result-object inheriting from. The classes don't actually have to implement Java inheritance, but that's probably the most common scenario. Let's extend the <tt>my.package.Employee</tt> class with a <tt>my.package.Manager</tt> class. The manager has an additional "team" attribute. In this example, the manager object will be populated with the TEAM column, as well as all the columns defined for for the Employee.</p>

			<pre class="brush: sql;">&lt;result-object id="Manager" class="my.package.Manager" extends="Employee"&gt;
	&lt;property name="team"&gt;
		&lt;column name="TEAM"/&gt;
	&lt;/property&gt;
&lt;/result-object&gt;</pre>
			<p>This reuses the Employee definition we made earlier.</p>

			<h2>Entity categorization</h2>
			<p>On the other side there's entity categorization or subtyping of entities. Take this example of a simple model of an order with rebate and item order lines.</p>
			<p class="Center"><img src="erd1.gif" alt="ERD example 1" /></p>
			<p>The subcategories are physically implemented as one-to-one relationships, with the TYPE column being the discriminator. A class model could be implemented like this:</p>
			<pre class="brush: scala;">public abstract class OrderLine {
	private Order order;
	private int sequence;
	
	public abstract BigDecimal calculateSomething();
}

public final class RebateOrderLine extends OrderLine {
	private Short percentage;
	
	public BigDecimal calculateSomething() {
		// Code calculating something
	}
}

public final class ItemOrderLine extends OrderLine {
	private Item item;
	private Integer quantity;

	public BigDecimal calculateSomething() {
		// Code calculating something
	}
}</pre>
			<p>Notice the <tt>OrderLine</tt> being abstract with the abstract method <tt>calculateSomething()</tt>.</p>

			<p>Let's assume that the discriminator values in the TYPE column, is <tt>'RBT'</tt> and <tt>'ITM'</tt>; then the result object mapping would look like this:</p>
			<pre class="brush: sql;">&lt;result-object id="OrderLine" class="my.package.OrderLine"&gt;
	&lt;delegate-mapping use-column="TYPE"&gt;
		&lt;delegate-to result-object="RebateLine" on-value="RBT"/&gt;

		&lt;delegate-to result-object="ItemLine" on-value="ITM"/&gt;
	&lt;/delegate-mapping&gt;
	&lt;!-- setters left out --&gt;
&lt;/result-object&gt;

&lt;result-object 
	id="RebateOrderLine" 
	class="my.package.RebateOrderLine"
	extends="OrderLine"&gt;
	&lt;!-- setters left out --&gt;

&lt;/result-object&gt;

&lt;result-object 
	id="ItemOrderLine" 
	class="my.package.ItemOrderLine" 						
	extends="OrderLine"&gt;
	&lt;!-- setters left out --&gt;
&lt;/result-object&gt;</pre>
			<p>The delegated result objects do not have to extend the <tt>result-object</tt> from where they are dynamically selected, but again, it's probably the most common use case.
The <tt>result-object</tt> containing the <tt>delegate-mapping</tt> definition is the default result object in case a discriminator value is not listed.</p>

			<p>It is now possible to write the following code using a predefined <tt>sql-statement</tt> with a <tt>result-object</tt> attribute of "OrderLine".</p>
			<pre class="brush: scala;">Query qry = broker.startQuery();
// set parameters here
OrderLine orderLine;
try {
	 orderLine = (OrderLine) qry.selectOne("getOrderLine");
} finally {
	qry.close();
}
BigDecimal calculation = orderLine.calculateSomething();</pre>
			<p>The correct subclass implementation is selected automatically based on the discriminator type.</p>
		</div>

<!--====================================================================-->
		<div id="dynamicStatements">
			<h1>Dynamic statements</h1>
			<p>So far, all we've seen is static hard-coded SQL. <em>O/R Broker</em> supports dynamic SQL through either <a href="http://jakarta.apache.org/velocity/user-guide.html">Velocity</a> or <a href="http://freemarker.sourceforge.net/docs/">FreeMarker</a>, two of the most popular template engines. When using a dynamic SQL statement, any parameter passed when executing will be part of the context for either template. The following Velocity example <tt>getEmployee.sql</tt> will select all employees, unless the <tt>id</tt> parameter is  passed.</p>

		<pre class="brush: sql;">SELECT 
	EmployeeId,
	EmployeeName,
	Salary,
	Currency,
	SSN
FROM 
	Employee
#if ($id)
	WHERE
		EmployeeId = :id
#end</pre>
		<p>Notice that the <tt>$id</tt> parameter is only used for testing its presence and then the <em>O/R Broker</em>-style parameter name is inserted (<tt>:id</tt>, not <tt>$id</tt>). This is because the statement must be evaluated as a prepared statement, where values are referenced instead of coded directly into the SQL. If they were coded directly, the programmer would have to take care of character escaping and quotes, and could leave the SQL vulnerable to hacking.</p>

		<p>Same example in FreeMarker:</p>
		<pre class="brush: sql;">SELECT 
	EmployeeId,
	EmployeeName,
	Salary,
	Currency,
	SSN
FROM 
	Employee
[#if id?exists ]
	WHERE
		EmployeeId = :id
[/#if]</pre>

		<p>For either type, the Scala code looks exactly the same as it would with static statements:</p>
		<pre class="brush: scala;">val emp = broker.readOnly() { session =>
  session.selectOne('getEmployee, "id"->1234)
}: Option[Employee]

// Or getting all employees

val emps = broker.readOnly() { session =>
  session.selectMany('getEmployee)
}: Seq[Employee]</pre>
		</div>
<!--====================================================================-->
		<div id="mappingAssociations">
			<h1>Mapping associations</h1>
			<h2>Joins</h2>

			<p>For a one-to-one association, use a SQL JOIN to join the tables, and then use the <tt>&lt;map-with&gt;</tt> element, which uses another <tt>result-object</tt> for mapping. Let's assume we have an organization with one manager.</p>
			<p class="Center"><img src="erd3.gif" alt="ERD 3"/></p>
			<pre class="brush: scala;">class Employee(id: Int, name: String)
			
class Organization(id: Int, manager: Employee)</pre>

	<p>The SQL file <tt>getOrganization</tt> looks like this:</p>

	<pre class="brush: sql;">SELECT 
	* 
FROM
	Organization O
JOIN
	Employee E
ON
	O.ManagerID = E.EmployeeID
WHERE
	O.OrganizationID = :organizationId
</pre>
<pre class="brush: scala;">
&lt;result-object id="Employee" class="my.package.Employee"&gt;
	&lt;property name="id"&gt;
		&lt;column name="EmployeeID"/&gt;
	&lt;/property&gt;

	&lt;property name="name"&gt;
		&lt;column name="Name"/&gt;
	&lt;/property&gt;
&lt;/result-object&gt;

&lt;result-object id="Organization" class="my.package.Organization"&gt;
	&lt;property name="id"&gt;

		&lt;column name="OrganizationID"/&gt;
	&lt;/property&gt;
	&lt;property name="manager"&gt;
		&lt;map-with result-object="Employee"/&gt;
	&lt;/property&gt;
&lt;/result-object&gt;</pre>

			<h2>Sub queries</h2>
			<p>We can also use a <tt>&lt;sub-query&gt;</tt> for mapping other result-objects, which is necessary when there are a one-to-many relationship. Let's use the same example as before, but assume that Employee also belongs to an Organization. </p>
			<p class="Center"><img src="erd4.gif" alt="ERD 4"/></p>
			<p>Employee class remains the same, but our Organization class would instead have a Set of employees. Something like this:</p>
			<pre class="brush: scala;">public class Organization {
	private Integer id;
	private Employee manager;
	private Set employees;
	
	// constructors, getters and setters implied.
}</pre>

		<p>Now, our mapping would look like this:</p>
		<pre class="brush: sql;">&lt;result-object id="Organization" class="my.package.Organization"&gt;
	&lt;property name="id"&gt;
		&lt;column name="OrganizationID"/&gt;
	&lt;/property&gt;
	&lt;property name="manager"&gt;

		&lt;map-with result-object="Employee"/&gt;
	&lt;/property&gt;
	&lt;property name="employees"&gt;
		&lt;sub-query sql-statement="selectEmployeesByOrganization"&gt;
			&lt;set-parameter name="organizationId" from-column="OrganizationID"/&gt;
		&lt;/sub-query&gt;

	&lt;/property&gt;
&lt;/result-object&gt;

&lt;sql-statement id="selectEmployees" result-object="Customer"&gt;
	SELECT
		*
	FROM
		Employee E
	&lt;append-statement id-suffix="ByOrganization"&gt;
	WHERE
		E.OrganizationID = :organizationId
	&lt;/append-statement&gt;
&lt;/sql-statement&gt;</pre>

		<p>Another <tt>sql-statement</tt> is invoked and the required parameters are set with column values from the current SELECT.</p>
		</div>
<!--====================================================================-->
		<div id="lazyInstantiation">
			<h1>Lazy instantiation</h1>
			<p>If we take the previous example, we can specify lazy instantiation of <tt>sub-query</tt> Collections.</p>

		<pre class="brush: sql;">&lt;result-object id="Customer" class="my.package.Customer"&gt;
	&lt;property name="shippingAddress"&gt;
		&lt;sub-query sql-statement="selectAddresses" lazy-load="true"&gt;
			&lt;set-parameter name="customer.id" from-column="CustomerID"&gt;
		&lt;/sub-query&gt;
	&lt;/property&gt;

	&lt;!-- Additional mapping goes here --&gt;
&lt;/result-object&gt;</pre>
			<p>Notice the <tt>lazy-load</tt> attribute, which delays the execution of the SQL statement until the Collection object is accessed. This will only work on Collection interfaces, not actual classes.</p>
			<p>The <tt>broker</tt> root element has a <tt>lazy-load</tt> attribute that sets lazy instantiation for all <tt>sub-query</tt> mappings. The default is <tt>true</tt>. Setting <tt>lazy-load</tt> on a <tt>sub-query</tt> element overrides the <tt>broker</tt> setting.</p>

		</div>
<!--====================================================================-->
		<div id="circularReferences">
			<h1>Circular references</h1>
			<p>Circular references are quite common in domain object modeling, so that should not be a limitation imposed by the persistence tool. Let's take an example of a Customer/ShippingAddress/Orders relationship.</p>
			<p class="Center"><img src="erd2.gif" alt="ERD example 2" /></p>
			<p>Using only public fields, the classes could look roughly like this:</p>
			<pre class="brush: scala;">public class Address {
	public Integer id;
	public Integer houseNumber;
	public String street;
	public String city;
	public Customer customer;
}

public class Customer {
	public Integer id;
	public String customerName;
	public Address shippingAddress;
	public Set orderHistory;
}

public class Order {
	public Integer id;
	public Customer customer;
	public Address shippingAddress;
}</pre>

			<p>The associations between these classes are heavily circular, with Customer having multiple Orders, and a current shipping Address. The Address having a Customer reference and the Order having both a Customer reference and an a shipping Address reference.</p>
			<p>The XML definition would look something like this:</p>
			<pre class="brush: sql;">&lt;!-- 
	This statement selects a customer by id and JOINs on the 
	one-to-one relationship to shipping address.
--&gt;
&lt;sql-statement id="selectCustomerById" result-object="Customer"&gt;
	SELECT
		*
	FROM
		Customer C
	JOIN
		Address A
	ON
		C.ShippingAddress = A.ShippingAddress
	WHERE
		C.CustomerID = :customerId
&lt;/sql-statement&gt;

&lt;!-- 
	This statement selects customer orders and JOINs on the 
	one-to-one relationship to customer and shipping
	address.
--&gt;

&lt;sql-statement id="selectOrdersByCustomer" result-object="Order"&gt;
	SELECT
		*
	FROM
		Orders O
	JOIN
		Customer C
	ON 
		O.CustomerID = C.CustomerID
	JOIN
		Address A
	ON
		O.ShippingAddressID = A.AddressID
	WHERE
		O.CustomerID = :customerId
&lt;/sql-statement&gt;

&lt;!-- 
	This result object maps some columns directly, but reuses
	the Customer result-object (defined below).
--&gt;
&lt;result-object id="Address" class="my.package.Address" key-columns="AddressID"&gt;
	&lt;field name="id"&gt;
		&lt;column name="AddressID"/&gt;

	&lt;/field&gt;
	&lt;field name="houseNumber"&gt;
		&lt;column name="HouseNumber"/&gt;
	&lt;/field&gt;
	&lt;field name="street"&gt;
		&lt;column name="Street"/&gt;

	&lt;/field&gt;
	&lt;field name="city"&gt;
		&lt;column name="City"/&gt;
	&lt;/field&gt;
	&lt;field name="customer"&gt;
		&lt;map-with result-object="Customer"/&gt;

	&lt;/field&gt;
&lt;/result-object&gt;

&lt;!-- 
	This result object maps some columns directly, but reuses
	both the Customer result-object and Address result-object.
--&gt;
&lt;result-object id="Order" class="my.package.Order" key-columns="OrderID"&gt;
	&lt;field name="id"&gt;
		&lt;column name="OrderID"/&gt;
	&lt;/field&gt;

	&lt;field name="customer"&gt;
		&lt;map-with result-object="Customer"/&gt;
	&lt;/field&gt;
	&lt;field name="shippingAddress"&gt;
		&lt;map-with result-object="Address"/&gt;
	&lt;/field&gt;

&lt;/result-object&gt;

&lt;!-- 
	This result object maps some columns directly, but reuses
	the Address result-object, and uses the previously defined
	SQL to map a Collection of Orders. 
--&gt;
&lt;result-object id="Customer" class="test.org.orbroker.Customer" key-columns="CustomerID"&gt;
	&lt;field name="id"&gt;
		&lt;column name="CUSTOMERID"/&gt;
	&lt;/field&gt;
	&lt;field name="name"&gt;

		&lt;column name="NAME"/&gt;
	&lt;/field&gt;
	&lt;field name="shippingAddress"&gt;
		&lt;map-with result-object="Address"/&gt;
	&lt;/field&gt;
	&lt;field name="orderHistory"&gt;

		&lt;sub-query sql-statement="selectOrdersByCustomer"&gt;
			&lt;set-parameter name="customerId" from-column="CUSTOMERID"/&gt;
		&lt;/sub-query&gt;
	&lt;/field&gt;
&lt;/result-object&gt;</pre>
		<p>Notice that all <tt>result-object</tt>s have the primary keys defined in the <tt>key-columns</tt> attribute. A table does not have to have a primary key defined, but the columns entered should be unique.</p>

		<p>The above definition now allows the following code:</p>
		<pre class="brush: scala;">Integer customerId = new Integer(6573);
Query qry = broker.startQuery();
qry.setParameter("customerId", customerId);
Customer customer;
try {
	customer = (Customer) qry.selectOne("selectCustomerById");
} finally {
	qry.close();
}
Iterator orders = customer.orderHistory.iterator();
while (order.hasNext()) {
	Order order = (Order) orders.next();
	boolean circularRef = 
		(customer == order.customer) &amp;&amp;
		(customer == customer.shippingAddress.customer);
	System.out.println("Circular references? " + circularRef);
}</pre>
			<p>The code above should result in <tt>"Circular references? true"</tt> being printed for as many times as there are customer orders.</p>
		</div>

		<div id="footer">
	<a href="http://validator.w3.org/check/referer">
		<img src="http://www.w3.org/Icons/valid-xhtml11" alt="Valid XHTML 1.1" style="border: none;"/>
	</a>

</div>

		
	</body>
</html>
